
 insert overwrite table jms_dm.dm_terminal_deliver_punctuality_rate_plan_sum_dt partition (dt)
 select
         end_agent_code,  -- 末端代理区code
        end_agent_name, --末端代理区
        end_center_code,  --末端中心code
       end_center_name, --末端中心名字
       end_city_id,  --城市id
       end_city_name, --城市名字
       final_deliver_network_code,  --派件网点code
       final_deliver_network_name,  --派件网点名字
       end_franchisee_code, --加盟商code
        end_franchisee_name,-- 加盟商
       order_source_code,  --订单来源code
       order_source_name, --订单来源
       is_common,  --是否共配
       need_deliver_count,  --应派件总量
       punctuality_sum, --及时派件总量
       one_planned_departure_time,  --一班规划接件时间
       one_plan_warehouse_end_time,  --一班规划出仓时间
       one_need_deliver_count,  -- 一班应出仓总量
       one_punctuality_sum,  --一班准点出仓量
       one_delay_30_minutes,  -- 一班延误30分钟量
       one_delay_60_minutes,  --一班延误30分钟量
       one_delay_120_minutes,  -- 一班延误120分钟总量
       two_planned_departure_time,  --二班规划接件时间
       two_plan_warehouse_end_time,  --二班规划出仓时间
       two_need_deliver_count,  -- 二班应出仓总量
       two_punctuality_sum,  --二班准点出仓量
       two_delay_30_minutes,  -- 二班延误30分钟量
       two_delay_60_minutes,  --二班延误30分钟量
       two_delay_120_minutes,  -- 二班延误120分钟总量
       is_township,  --1乡镇 0 非乡镇
       date_time,-- 业务日期
       case
           when nvl(one_plan_warehouse_end_time, '0:00:00') = '08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:00:00'  then one_need_deliver_count + two_need_deliver_count + three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') = '08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00')!= '08:00:00'  then one_need_deliver_count + two_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') = '08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:00:00'  then one_need_deliver_count + three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') !='08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:00:00'  then two_need_deliver_count + three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') = '08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '08:00:00' then one_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') !='08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '08:00:00' then two_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') !='08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:00:00'  then three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') !='08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '08:00:00' then 0 end
           as deliver_800_count,

       case
           when nvl(one_plan_warehouse_end_time, '0:00:00') = '08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:00:00' then one_punctuality_sum + two_punctuality_sum + three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') = '08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') !='08:00:00' then one_punctuality_sum + two_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') = '08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:00:00' then one_punctuality_sum + three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') !='08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:00:00' then two_punctuality_sum + three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') = '08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') !='08:00:00' then one_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') !='08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') !='08:00:00' then two_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') !='08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:00:00' then three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') !='08:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:00:00' and
                nvl(three_plan_warehouse_end_time, '00:00:00') != '08:00:00' then 0 end
           as on_time_deliver_800_count,
       case
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:30:00' then one_need_deliver_count + two_need_deliver_count + three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '08:30:00'  then one_need_deliver_count + two_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:30:00'   then one_need_deliver_count + three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:30:00'   then two_need_deliver_count + three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '08:30:00'  then one_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00')  != '08:30:00' then two_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:30:00'   then three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '08:30:00'   then 0 end
           as deliver_830_count,
       case
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:30:00'  then one_punctuality_sum + two_punctuality_sum + three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '08:30:00' then one_punctuality_sum + two_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:30:00' then one_punctuality_sum + three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:30:00' then two_punctuality_sum + three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '08:30:00' then one_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '08:30:00' then two_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '08:30:00' then three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '08:30:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '08:30:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '08:30:00' then 0 end
           as on_time_deliver_830_count,
       case
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '09:00:00' then one_need_deliver_count + two_need_deliver_count + three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '09:00:00' then one_need_deliver_count + two_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '09:00:00' and  nvl(three_plan_warehouse_end_time, '00:00:00') = '09:00:00' then one_need_deliver_count + three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '09:00:00' and  nvl(two_plan_warehouse_end_time, '00:00:00') = '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '09:00:00'  then two_need_deliver_count + three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '09:00:00' then one_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '09:00:00' and  nvl(two_plan_warehouse_end_time, '00:00:00') = '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '09:00:00' then two_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '09:00:00' then three_need_deliver_count
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '09:00:00' then 0 end as deliver_900_count,
       case
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '09:00:00' then one_punctuality_sum + two_punctuality_sum + three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '09:00:00' then one_punctuality_sum + two_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '09:00:00' then one_punctuality_sum + three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') = '09:00:00' then two_punctuality_sum + three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00')  = '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '09:00:00' then one_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00')  = '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '09:00:00' then two_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '09:00:00' and  nvl(three_plan_warehouse_end_time, '00:00:00')  = '09:00:00' then three_punctuality_sum
           when nvl(one_plan_warehouse_end_time, '0:00:00') != '09:00:00' and nvl(two_plan_warehouse_end_time, '00:00:00') != '09:00:00' and nvl(three_plan_warehouse_end_time, '00:00:00') != '09:00:00' then 0 end
           as on_time_deliver_900_count,
       -- 增加三派
       three_planned_departure_time,  --一班规划接件时间
       three_plan_warehouse_end_time, --一班规划出仓时间
       three_need_deliver_count,      --一班应出仓总量
       three_punctuality_sum,         --一班准点出仓量
       three_delay_30_minutes,        --一班延误30分钟量
       three_delay_60_minutes,        --一班延误60分钟量
       three_delay_120_minutes,        --一班延误120分钟总量
        final_sign_user_code,
        final_sign_user_name,
       dt
from (
    select end_agent_code,                                                                                                        -- 末端代理区code
           max(end_agent_name) as                                             end_agent_name,                                     --末端代理区
           end_center_code,                                                                                                       --末端中心code
           max(end_center_name) as                                            end_center_name,                                    --末端中心名字
           end_city_id,                                                                                                           --城市id
           max(end_city_name) as                                              end_city_name,                                      --城市名字
           final_deliver_network_code,                                                                                            --派件网点code
           max(final_deliver_network_name) as                                 final_deliver_network_name,                         --派件网点名字
           end_franchisee_code,                                                                                                   --加盟商code
           max(end_franchisee_name) as                                        end_franchisee_name,-- 加盟商
           order_source_code,                                                                                                     --订单来源code
           max(order_source_name) as                                          order_source_name,                                  --订单来源
           max(is_common) as                                                  is_common,                                          --是否共配
           count(1) as                                                        need_deliver_count,                                 --应派件总量
           sum(is_deliver_punctuality) as                                     punctuality_sum,                                    --及时派件总量
           max(if(deliver_shift = 1, planned_departure_time, null)) as        one_planned_departure_time,                         --一班规划接件时间
           max(if(deliver_shift = 1, plan_warehouse_end_time, null)) as       one_plan_warehouse_end_time,                        --一班规划出仓时间
           sum(if(deliver_shift = 1, 1, 0)) as                                one_need_deliver_count,                             -- 一班应出仓总量
           sum(if(deliver_shift = 1 and is_deliver_punctuality = 1, 1, 0)) as one_punctuality_sum,                                --一班准点出仓量
           sum(if(deliver_shift = 1 and delay_minutes is not null and delay_minutes <= 30, 1, 0)) as    one_delay_30_minutes,                               -- 一班延误30分钟量
           sum(if(deliver_shift = 1 and delay_minutes is not null and delay_minutes > 30 and delay_minutes <= 60, 1, 0)) as  one_delay_60_minutes,                               --一班延误30分钟量
           sum(if(deliver_shift = 1 and delay_minutes is not null and delay_minutes > 60 and delay_minutes <= 120, 1, 0)) as  one_delay_120_minutes,                              -- 一班延误120分钟总量
           max(if(deliver_shift = 2, planned_departure_time, null)) as        two_planned_departure_time,                         --二班规划接件时间
           max(if(deliver_shift = 2, plan_warehouse_end_time, null)) as       two_plan_warehouse_end_time,                        --二班规划出仓时间
           sum(if(deliver_shift = 2, 1, 0)) as                                two_need_deliver_count,                             -- 二班应出仓总量
           sum(if(deliver_shift = 2 and is_deliver_punctuality = 1, 1, 0)) as two_punctuality_sum,                                --二班准点出仓量
           sum(if(deliver_shift = 2 and delay_minutes is not null and delay_minutes <= 30, 1, 0)) as                              two_delay_30_minutes,                               -- 二班延误30分钟量
           sum(if(deliver_shift = 2 and delay_minutes is not null and delay_minutes > 30 and delay_minutes <= 60, 1, 0)) as       two_delay_60_minutes,                               --二班延误30分钟量
           sum(if(deliver_shift = 2 and delay_minutes is not null and delay_minutes > 60 and delay_minutes <= 120,1, 0)) as       two_delay_120_minutes,                              -- 二班延误120分钟总量
           -- 增加三派
           max(if(deliver_shift = 3, planned_departure_time, null)) as        three_planned_departure_time,                       --一班规划接件时间
           max(if(deliver_shift = 3, plan_warehouse_end_time, null)) as       three_plan_warehouse_end_time,                      --一班规划出仓时间
           sum(if(deliver_shift = 3, 1, 0)) as                                three_need_deliver_count,                           -- 一班应出仓总量
           sum(if(deliver_shift = 3 and is_deliver_punctuality = 1, 1, 0)) as three_punctuality_sum,                              --一班准点出仓量
           sum(if(deliver_shift = 3 and delay_minutes is not null and delay_minutes <= 30, 1, 0)) as                             three_delay_30_minutes,    -- 一班延误30分钟量
           sum(if(deliver_shift = 3 and delay_minutes is not null and delay_minutes > 30 and delay_minutes <= 60, 1, 0)) as     three_delay_60_minutes,     --一班延误30分钟量
           sum(if(deliver_shift = 3 and delay_minutes is not null and delay_minutes > 60 and delay_minutes <= 120, 1, 0)) as    three_delay_120_minutes,     -- 一班延误120分钟总量
           is_township,                                                                                                           --1乡镇 0 非乡镇
           new_effective_plan_deliver_date as                                 date_time,-- 业务日期
           final_sign_user_code,
           max(final_sign_user_name) as final_sign_user_name,
--        sum(if(substring(new_effective_plan_sign_time,12,8)='08:00:00',1,0)) as deliver_800_count,
--        sum(if(substring(new_effective_plan_sign_time,12,8)='08:00:00'  and is_deliver_punctuality=1 ,1,0)) as on_time_deliver_800_count,
--        sum(if(substring(new_effective_plan_sign_time,12,8)='08:30:00',1,0)) as deliver_830_count,
--        sum(if(substring(new_effective_plan_sign_time,12,8)='08:30:00'  and is_deliver_punctuality=1,1,0)) as on_time_deliver_830_count,
--        sum(if(substring(new_effective_plan_sign_time,12,8)='09:00:00',1,0)) as deliver_900_count,
--        sum(if(substring(new_effective_plan_sign_time,12,8)='09:00:00'  and is_deliver_punctuality=1,1,0)) as on_time_deliver_900_count,
           new_effective_plan_deliver_date as                                 dt
    from jms_dm.dm_terminal_deliver_punctuality_rate_detail_dt
    where dt >= date_sub('{{ execution_date  | cst_ds }}', 20)
      and dt <= '{{ execution_date  | cst_ds }}'
      and new_effective_plan_deliver_date >= date_sub('{{ execution_date  | cst_ds }}', 14)
      and new_effective_plan_deliver_date <= '{{ execution_date  | cst_ds }}'
    group by new_effective_plan_deliver_date,
             end_agent_code,
             end_center_code,
             end_franchisee_code,
             end_city_id,
             final_deliver_network_code,
             order_source_code,
             is_township,
             final_sign_user_code
) tmp
    distribute by dt
   , pmod(hash(rand()), 4);